package com.coolwen.controller;

import com.coolwen.entity.ExpertAndCouncilor;
import com.coolwen.service.councilor.CouncilorService;
import com.coolwen.service.expertAndCouncilor.ExpertAndCouncilorService;
import com.coolwen.service.expert.ExpertService;
import com.coolwen.service.student.StudentService;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by nieyu on 2016/8/8.
 */
@Controller
@RequestMapping("/expert")
public class ExpertController {

    @Autowired
    ExpertService expertService;
    @Autowired
    StudentService studentService;
    @Autowired
    CouncilorService councilorService;
    @Autowired
    ExpertAndCouncilorService expertAndCouncilorService;

    @RequestMapping(value = "/expert_info", method = RequestMethod.GET)
    public String expertMessage(Model model, String grade, String department, String major, HttpServletRequest request) {

        model.addAttribute("g", grade);
        model.addAttribute("d", department);
        model.addAttribute("m", major);

        if (department != null) {
            String subDepartment = request.getParameter("department");
            department = subDepartment.substring(0, 2);
        }

        model.addAttribute("pagers", expertAndCouncilorService.find(grade, department, major));

        return "page/expert";

    }

    /*
    *专家评教信息导出
     */
    @RequestMapping(value = "/out_info", method = RequestMethod.GET)
    public String putStudentInfo(String grade, String department, String major, HttpServletResponse response) throws IOException, WriteException {

//        本地测试用，固定路径
//        FileOutputStream fileOutputStream = new FileOutputStream("d:/expert.xls");
//        WritableWorkbook wwb;

        SimpleDateFormat dt = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = dt.format(new Date());//获取当前时间

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流

        response.setHeader("Content-disposition", "attachment; filename=" + new String(date.getBytes("GB2312"), "8859_1") + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wwb;
        List<ExpertAndCouncilor> expertAndCouncilors = expertAndCouncilorService.findAll(grade, department, major);


        try {
            //设置单元格文本自动换行
            WritableFont fontTitle = new WritableFont(WritableFont.TIMES, 9, WritableFont.NO_BOLD);
            WritableCellFormat formatTitle = new WritableCellFormat(fontTitle);
            formatTitle.setWrap(true);

            WritableCellFormat headerFormat = new WritableCellFormat(
                    fontTitle);

            //设置单元格文本居中显示

            //水平居中
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);


            wwb = Workbook.createWorkbook(os);
            WritableSheet ws = wwb.createSheet("督导专家汇总", 0);

            ws.mergeCells(1, 0, 18, 1);//合并单元格，第一个参数：要合并的单元格最左上角的列号，
            // 第二个参数：要合并的单元格最左上角的行号，
            // 第三个参数：要合并的单元格最右角的列号，
            // 第四个参数：要合并的单元格最右下角的行号，
            ws.mergeCells(0, 0, 0, 1);

            ws.setColumnView(1, 15);

            for (int i = 0; i < 1; i++) {
                ExpertAndCouncilor expertAndCouncilor = expertAndCouncilors.get(i);
                ws.addCell(new Label(1, 0, "四川旅游学院本科(" + expertAndCouncilor.getGrade() + "级" + expertAndCouncilor.getMajor() + "专业）专业课程质量（二级督导/专家组）评价统计表", headerFormat));
            }
            ws.mergeCells(0, 2, 0, 3);
            ws.addCell(new Label(0, 2, "序号", headerFormat));
            ws.mergeCells(1, 2, 1, 3);
            ws.addCell(new Label(1, 2, "课程名称", headerFormat));
            ws.mergeCells(2, 2, 5, 2);
            ws.addCell(new Label(2, 2, "第一学年（上/下）", headerFormat));
            ws.addCell(new Label(2, 3, "二级督导"));
            ws.addCell(new Label(3, 3, "校级专家"));
            ws.addCell(new Label(4, 3, "二级督导"));
            ws.addCell(new Label(5, 3, "校级专家"));
            ws.mergeCells(6, 2, 9, 2);
            ws.addCell(new Label(6, 2, "第二学年（上/下）", headerFormat));
            ws.addCell(new Label(6, 3, "二级督导"));
            ws.addCell(new Label(7, 3, "校级专家"));
            ws.addCell(new Label(8, 3, "二级督导"));
            ws.addCell(new Label(9, 3, "校级专家"));
            ws.mergeCells(10, 2, 13, 2);
            ws.addCell(new Label(10, 2, "第三学年（上/下）", headerFormat));
            ws.addCell(new Label(10, 3, "二级督导"));
            ws.addCell(new Label(11, 3, "校级专家"));
            ws.addCell(new Label(12, 3, "二级督导"));
            ws.addCell(new Label(13, 3, "校级专家"));
            ws.mergeCells(14, 2, 17, 2);
            ws.addCell(new Label(14, 2, "第四学年（上/下）", headerFormat));
            ws.addCell(new Label(14, 3, "二级督导"));
            ws.addCell(new Label(15, 3, "校级专家"));
            ws.addCell(new Label(16, 3, "二级督导"));
            ws.addCell(new Label(17, 3, "校级专家"));
            ws.mergeCells(18, 2, 18, 3);
            ws.addCell(new Label(18, 2, "总评得分", headerFormat));


            for (int i = 0; i < expertAndCouncilors.size(); i++) {
                int b = i + 1;
                ExpertAndCouncilor expertAndCouncilor = expertAndCouncilors.get(i);
                ws.addCell(new Label(0, i + 4, "" + b, headerFormat));
                ws.addCell(new Label(1, i + 4, "" + expertAndCouncilor.getCourse()));

                if (expertAndCouncilor.getSemester().contains("第一学年") && expertAndCouncilor.getSemester().contains("第一学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(2, i + 4, ""));
                    } else {
                        ws.addCell(new Label(2, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(3, i + 4, ""));
                    } else {
                        ws.addCell(new Label(3, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                } else if (expertAndCouncilor.getSemester().contains("第一学年") && expertAndCouncilor.getSemester().contains("第二学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(4, i + 4, ""));
                    } else {
                        ws.addCell(new Label(4, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(5, i + 4, ""));
                    } else {
                        ws.addCell(new Label(5, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                } else if (expertAndCouncilor.getSemester().contains("第二学年") && expertAndCouncilor.getSemester().contains("第一学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(6, i + 4, ""));
                    } else {
                        ws.addCell(new Label(6, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(7, i + 4, ""));
                    } else {
                        ws.addCell(new Label(7, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }

                } else if (expertAndCouncilor.getSemester().contains("第二学年") && expertAndCouncilor.getSemester().contains("第二学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(8, i + 4, ""));
                    } else {
                        ws.addCell(new Label(8, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(9, i + 4, ""));
                    } else {
                        ws.addCell(new Label(9, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                } else if (expertAndCouncilor.getSemester().contains("第三学年") && expertAndCouncilor.getSemester().contains("第一学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(10, i + 4, ""));
                    } else {
                        ws.addCell(new Label(10, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(11, i + 4, ""));
                    } else {
                        ws.addCell(new Label(11, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                } else if (expertAndCouncilor.getSemester().contains("第三学年") && expertAndCouncilor.getSemester().contains("第二学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(12, i + 4, ""));
                    } else {
                        ws.addCell(new Label(12, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(13, i + 4, ""));
                    } else {
                        ws.addCell(new Label(13, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                } else if (expertAndCouncilor.getSemester().contains("第四学年") && expertAndCouncilor.getSemester().contains("第一学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(14, i + 4, ""));
                    } else {
                        ws.addCell(new Label(14, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(15, i + 4, ""));
                    } else {
                        ws.addCell(new Label(15, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                } else if (expertAndCouncilor.getSemester().contains("第二学年") && expertAndCouncilor.getSemester().contains("第一学期")) {
                    if (expertAndCouncilor.getCouncilorScore() == null) {
                        ws.addCell(new Label(16, i + 4, ""));
                    } else {
                        ws.addCell(new Label(16, i + 4, String.valueOf(expertAndCouncilor.getCouncilorScore()), headerFormat));
                    }
                    if (expertAndCouncilor.getExpertScore() == null) {
                        ws.addCell(new Label(17, i + 4, ""));
                    } else {
                        ws.addCell(new Label(17, i + 4, "" + String.valueOf(expertAndCouncilor.getExpertScore()), headerFormat));
                    }
                }
                if (expertAndCouncilor.getCouncilorScore() != null && expertAndCouncilor.getExpertScore() != null) {
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format((expertAndCouncilor.getExpertScore() * 1.3 + expertAndCouncilor.getCouncilorScore()) / 2.3);
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (expertAndCouncilor.getCouncilorScore() == null && expertAndCouncilor.getExpertScore() != null) {
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(expertAndCouncilor.getExpertScore());
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
                if (expertAndCouncilor.getCouncilorScore() != null && expertAndCouncilor.getExpertScore() == null) {
                    DecimalFormat fnum = new DecimalFormat("##0.00");
                    String dd = fnum.format(expertAndCouncilor.getCouncilorScore());
                    ws.addCell(new Label(18, i + 4, dd, headerFormat));
                }
            }

            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return "page/out";

    }

    /*
    *专家评教模板下载
     */
    @RequestMapping(value = "/download", method = RequestMethod.GET)
    public void downloadExpert(HttpServletResponse response) throws IOException, WriteException {

        SimpleDateFormat dt = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = dt.format(new Date());//获取当前时间

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流

        response.setHeader("Content-disposition", "attachment; filename=" + new String("专家评教模板".getBytes("GB2312"), "8859_1") + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wwb;

        try {
            //设置单元格文本自动换行
            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                    12,
                    WritableFont.BOLD,
                    false,
                    UnderlineStyle.NO_UNDERLINE);

            WritableCellFormat formatTitles = new WritableCellFormat(font);
            formatTitles.setWrap(true);

            WritableCellFormat headerFormat = new WritableCellFormat(
                    font);

            //设置单元格文本居中显示

            //水平居中
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);


            wwb = Workbook.createWorkbook(os);
            WritableSheet ws = wwb.createSheet("校级专家评教模板", 0);

            ws.setColumnView(3, 15);
            ws.setColumnView(5, 15);
            ws.setColumnView(4, 15);
            ws.setColumnView(8, 15);
            ws.setColumnView(9, 15);

            ws.addCell(new Label(0, 0, "序号", headerFormat));

            ws.addCell(new Label(1, 0, "系部", headerFormat));

            ws.addCell(new Label(2, 0, "专业", headerFormat));

            ws.addCell(new Label(3, 0, "年级", headerFormat));

            ws.addCell(new Label(4, 0, "授课教师[单位]", headerFormat));

            ws.addCell(new Label(5, 0, "课程名称", headerFormat));

            ws.addCell(new Label(6, 0, "日期（节次）", headerFormat));

            ws.addCell(new Label(7, 0, "教室", headerFormat));

            ws.addCell(new Label(8, 0, "班级", headerFormat));

            ws.addCell(new Label(9, 0, "实到人数", headerFormat));

            ws.addCell(new Label(10, 0, "综合得分", headerFormat));

            ws.addCell(new Label(11, 0, "备注", headerFormat));

            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}
